{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas:数据规整"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "内容介绍:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "d    0\n",
      "b    1\n",
      "c    2\n",
      "a    3\n",
      "e    4\n",
      "d    5\n",
      "b    6\n",
      "c    7\n",
      "a    8\n",
      "e    9\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>-7</td>\n",
       "      <td>-1</td>\n",
       "      <td>-9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>-6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>8</td>\n",
       "      <td>-5</td>\n",
       "      <td>-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>-1</td>\n",
       "      <td>-7</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   B  A  C\n",
       "d -7 -1 -9\n",
       "b  2 -6  6\n",
       "c  8 -5 -4\n",
       "a -1 -7 -1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 示例数据\n",
    "s0 = pd.Series(range(10),index=['d','b','c','a','e','d','b','c','a','e'])\n",
    "print(s0)\n",
    "df0 = pd.DataFrame(np.random.randint(-9,9,size=(4,3)),index=['d','b','c','a'],columns=['B','A','C'])\n",
    "df0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.层次化索引-知识回顾(可以查看06文档详细讲解)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#添加一个层次化索引\n",
    "dic = {'a':1,'b':2,'c':3,'d':4,'e':5}\n",
    "s0_index_2 = s0.index.map(dic)\n",
    "#构建一个新的带有层次化索引的数据\n",
    "s = pd.Series(s0.values,index=[s0.index, s0_index_2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([4, 2, 3, 1, 5, 4, 2, 3, 1, 5], dtype='int64')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s0_index_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 索引排序\n",
    "s1 = s.sort_index()\n",
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a  1    3\n",
       "   1    8\n",
       "b  2    1\n",
       "   2    6\n",
       "c  3    2\n",
       "   3    7\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#层次化索引的选取操作\n",
    "s1['a':'c']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b  2    1\n",
       "   2    6\n",
       "d  4    0\n",
       "   4    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#离散选取\n",
    "s1[['b','d']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b    1\n",
       "b    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#直接索引内层\n",
    "s1.loc[:,2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method DataFrame.sort_index of            B  A  C\n",
       "new_idx           \n",
       "4       d -8 -2 -2\n",
       "2       b -1  2 -2\n",
       "3       c  7  6 -1\n",
       "1       a -1  3  5>"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#设置新索引\n",
    "df_idx = df0.index.map(dic)\n",
    "df0['new_idx'] = df_idx\n",
    "#set_index()函数中的参数,drop=False时原来转换成索引的列继续保留\n",
    "df1 = df0.set_index(['new_idx',df0.index])\n",
    "df1.sort_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method DataFrame.reset_index of            B  A  C\n",
       "new_idx           \n",
       "4       d -8 -2 -2\n",
       "2       b -1  2 -2\n",
       "3       c  7  6 -1\n",
       "1       a -1  3  5>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将转换成索引的列还原成原来的列\n",
    "df1.reset_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([(4, 'd'),\n",
       "            (2, 'b'),\n",
       "            (3, 'c'),\n",
       "            (1, 'a')],\n",
       "           names=['new_idx', None])"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.数据连接-连接列的方式\n",
    "\n",
    "merge(left, right, how: str = 'inner', on=None, left_on=None, right_on=None, left_index: bool = False, right_index: bool = False, sort: bool = False, suffixes=('_x', '_y'), copy: bool = True, indicator: bool = False, validate=None) -> 'DataFrame'\n",
    "\n",
    "* left,需要连接的左方数据\n",
    "* right,需要连接的右方数据\n",
    "* how:连接方式，包括inner,outer,left,right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>yy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>mm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>snn</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   D  E  F  key\n",
       "0  5  8  1   yy\n",
       "1  3  0  1   xx\n",
       "2  7  4  4   mm\n",
       "3  5  0  7  snn"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df21 = pd.DataFrame(np.random.randint(0,9,size=(4,3)),columns=['D','E','F'])\n",
    "df21['key'] = ['yy','xx','mm','snn']\n",
    "df21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>xx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>yy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>mm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>nn</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   B  A  C key\n",
       "0  1  4  7  xx\n",
       "1  6  4  7  yy\n",
       "2  0  8  3  mm\n",
       "3  8  4  0  nn"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df22 = pd.DataFrame(np.random.randint(0,9,size=(4,3)),columns=['B','A','C'])\n",
    "df22['key'] = ['xx','yy','mm','nn']\n",
    "df22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>mm</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   D  E  F key  B  A  C\n",
       "0  5  8  1  yy  6  4  7\n",
       "1  3  0  1  xx  1  4  7\n",
       "2  7  4  4  mm  0  8  3"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.merge根据单个或多个键连接两个表格。默认列明相同的列为连接键。\n",
    "#需要关键列一致\n",
    "#也可以使用参数on设定需要连接的键列\n",
    "#默认：在键列中，两者不一致的数据，不会被连接。即内连接形式。\n",
    "pd.merge(df21,df22)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>mm</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   D  E  F key  B  A  C\n",
       "0  5  8  1  yy  6  4  7\n",
       "1  3  0  1  xx  1  4  7\n",
       "2  7  4  4  mm  0  8  3"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#内连接，指定参数进行。内连接也是默认值。-->交集的概念\n",
    "pd.merge(df21,df22,how='inner',on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>yy</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>mm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>snn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   D  E  F  key    B    A    C\n",
       "0  5  8  1   yy  6.0  4.0  7.0\n",
       "1  3  0  1   xx  1.0  4.0  7.0\n",
       "2  7  4  4   mm  0.0  8.0  3.0\n",
       "3  5  0  7  snn  NaN  NaN  NaN"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#左连接\n",
    "#以前面(即左面的)的参数为主，查找右面的数据，存在相同的则连接数据；如果左边键有数据，右边没有那么不连接数据。\n",
    "pd.merge(df21,df22,how='left',on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>xx</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>mm</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nn</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     D    E    F key  B  A  C\n",
       "0  3.0  0.0  1.0  xx  1  4  7\n",
       "1  5.0  8.0  1.0  yy  6  4  7\n",
       "2  7.0  4.0  4.0  mm  0  8  3\n",
       "3  NaN  NaN  NaN  nn  8  4  0"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#右连接，方式同左连接相反。\n",
    "pd.merge(df21,df22,how='right',on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>yy</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>xx</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>mm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>snn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nn</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     D    E    F  key    B    A    C\n",
       "0  5.0  8.0  1.0   yy  6.0  4.0  7.0\n",
       "1  3.0  0.0  1.0   xx  1.0  4.0  7.0\n",
       "2  7.0  4.0  4.0   mm  0.0  8.0  3.0\n",
       "3  5.0  0.0  7.0  snn  NaN  NaN  NaN\n",
       "4  NaN  NaN  NaN   nn  8.0  4.0  0.0"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#外连接，方式为两者结合最大的范围。-->并集的概念\n",
    "pd.merge(df21,df22,how='outer',on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>yy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>mm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>snn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>yy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>99</td>\n",
       "      <td>yy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>13</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>yy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D   E   F  key\n",
       "0   5   8   1   yy\n",
       "1   3   0   1   xx\n",
       "2   7   4   4   mm\n",
       "3   5   0   7  snn\n",
       "4   3   5   6   yy\n",
       "5   3  50  99   yy\n",
       "6  13  22   6   yy"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#连接中处理重复的列名\n",
    "df23 = df21.copy()\n",
    "df23.loc['4'] = [3,5,6,'yy']\n",
    "df23.loc['5'] = [3,50,99,'yy']\n",
    "df23.loc['6'] = [13,22,6,'yy']\n",
    "df23"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>99</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>yy</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>mm</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    D   E   F key  B  A  C\n",
       "0   5   8   1  yy  6  4  7\n",
       "1   3   5   6  yy  6  4  7\n",
       "2   3  50  99  yy  6  4  7\n",
       "3  13  22   6  yy  6  4  7\n",
       "4   3   0   1  xx  1  4  7\n",
       "5   7   4   4  mm  0  8  3"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#数据中出现重复的key关键字时，处理方式如下：\n",
    "#关键字匹配时，没有关键字重复的数据进行多次匹配\n",
    "pd.merge(df23,df22)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>key_x</th>\n",
       "      <th>B</th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "      <th>key_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>xx</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>xx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>snn</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>xx</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   D  E  F key_x  B  A  C key_y\n",
       "1  3  0  1    xx  1  4  7    xx\n",
       "3  5  0  7   snn  1  4  7    xx"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#按索引连接和关键字key连接。默认使用内连接。\n",
    "pd.merge(df23,df22,left_on='E',right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function merge in module pandas.core.reshape.merge:\n",
      "\n",
      "merge(left, right, how: str = 'inner', on=None, left_on=None, right_on=None, left_index: bool = False, right_index: bool = False, sort: bool = False, suffixes=('_x', '_y'), copy: bool = True, indicator: bool = False, validate=None) -> 'DataFrame'\n",
      "    Merge DataFrame or named Series objects with a database-style join.\n",
      "    \n",
      "    The join is done on columns or indexes. If joining columns on\n",
      "    columns, the DataFrame indexes *will be ignored*. Otherwise if joining indexes\n",
      "    on indexes or indexes on a column or columns, the index will be passed on.\n",
      "    When performing a cross merge, no column specifications to merge on are\n",
      "    allowed.\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    left : DataFrame\n",
      "    right : DataFrame or named Series\n",
      "        Object to merge with.\n",
      "    how : {'left', 'right', 'outer', 'inner', 'cross'}, default 'inner'\n",
      "        Type of merge to be performed.\n",
      "    \n",
      "        * left: use only keys from left frame, similar to a SQL left outer join;\n",
      "          preserve key order.\n",
      "        * right: use only keys from right frame, similar to a SQL right outer join;\n",
      "          preserve key order.\n",
      "        * outer: use union of keys from both frames, similar to a SQL full outer\n",
      "          join; sort keys lexicographically.\n",
      "        * inner: use intersection of keys from both frames, similar to a SQL inner\n",
      "          join; preserve the order of the left keys.\n",
      "        * cross: creates the cartesian product from both frames, preserves the order\n",
      "          of the left keys.\n",
      "    \n",
      "          .. versionadded:: 1.2.0\n",
      "    \n",
      "    on : label or list\n",
      "        Column or index level names to join on. These must be found in both\n",
      "        DataFrames. If `on` is None and not merging on indexes then this defaults\n",
      "        to the intersection of the columns in both DataFrames.\n",
      "    left_on : label or list, or array-like\n",
      "        Column or index level names to join on in the left DataFrame. Can also\n",
      "        be an array or list of arrays of the length of the left DataFrame.\n",
      "        These arrays are treated as if they are columns.\n",
      "    right_on : label or list, or array-like\n",
      "        Column or index level names to join on in the right DataFrame. Can also\n",
      "        be an array or list of arrays of the length of the right DataFrame.\n",
      "        These arrays are treated as if they are columns.\n",
      "    left_index : bool, default False\n",
      "        Use the index from the left DataFrame as the join key(s). If it is a\n",
      "        MultiIndex, the number of keys in the other DataFrame (either the index\n",
      "        or a number of columns) must match the number of levels.\n",
      "    right_index : bool, default False\n",
      "        Use the index from the right DataFrame as the join key. Same caveats as\n",
      "        left_index.\n",
      "    sort : bool, default False\n",
      "        Sort the join keys lexicographically in the result DataFrame. If False,\n",
      "        the order of the join keys depends on the join type (how keyword).\n",
      "    suffixes : list-like, default is (\"_x\", \"_y\")\n",
      "        A length-2 sequence where each element is optionally a string\n",
      "        indicating the suffix to add to overlapping column names in\n",
      "        `left` and `right` respectively. Pass a value of `None` instead\n",
      "        of a string to indicate that the column name from `left` or\n",
      "        `right` should be left as-is, with no suffix. At least one of the\n",
      "        values must not be None.\n",
      "    copy : bool, default True\n",
      "        If False, avoid copy if possible.\n",
      "    indicator : bool or str, default False\n",
      "        If True, adds a column to the output DataFrame called \"_merge\" with\n",
      "        information on the source of each row. The column can be given a different\n",
      "        name by providing a string argument. The column will have a Categorical\n",
      "        type with the value of \"left_only\" for observations whose merge key only\n",
      "        appears in the left DataFrame, \"right_only\" for observations\n",
      "        whose merge key only appears in the right DataFrame, and \"both\"\n",
      "        if the observation's merge key is found in both DataFrames.\n",
      "    \n",
      "    validate : str, optional\n",
      "        If specified, checks if merge is of specified type.\n",
      "    \n",
      "        * \"one_to_one\" or \"1:1\": check if merge keys are unique in both\n",
      "          left and right datasets.\n",
      "        * \"one_to_many\" or \"1:m\": check if merge keys are unique in left\n",
      "          dataset.\n",
      "        * \"many_to_one\" or \"m:1\": check if merge keys are unique in right\n",
      "          dataset.\n",
      "        * \"many_to_many\" or \"m:m\": allowed, but does not result in checks.\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    DataFrame\n",
      "        A DataFrame of the two merged objects.\n",
      "    \n",
      "    See Also\n",
      "    --------\n",
      "    merge_ordered : Merge with optional filling/interpolation.\n",
      "    merge_asof : Merge on nearest keys.\n",
      "    DataFrame.join : Similar method using indices.\n",
      "    \n",
      "    Notes\n",
      "    -----\n",
      "    Support for specifying index levels as the `on`, `left_on`, and\n",
      "    `right_on` parameters was added in version 0.23.0\n",
      "    Support for merging named Series objects was added in version 0.24.0\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df1 = pd.DataFrame({'lkey': ['foo', 'bar', 'baz', 'foo'],\n",
      "    ...                     'value': [1, 2, 3, 5]})\n",
      "    >>> df2 = pd.DataFrame({'rkey': ['foo', 'bar', 'baz', 'foo'],\n",
      "    ...                     'value': [5, 6, 7, 8]})\n",
      "    >>> df1\n",
      "        lkey value\n",
      "    0   foo      1\n",
      "    1   bar      2\n",
      "    2   baz      3\n",
      "    3   foo      5\n",
      "    >>> df2\n",
      "        rkey value\n",
      "    0   foo      5\n",
      "    1   bar      6\n",
      "    2   baz      7\n",
      "    3   foo      8\n",
      "    \n",
      "    Merge df1 and df2 on the lkey and rkey columns. The value columns have\n",
      "    the default suffixes, _x and _y, appended.\n",
      "    \n",
      "    >>> df1.merge(df2, left_on='lkey', right_on='rkey')\n",
      "      lkey  value_x rkey  value_y\n",
      "    0  foo        1  foo        5\n",
      "    1  foo        1  foo        8\n",
      "    2  foo        5  foo        5\n",
      "    3  foo        5  foo        8\n",
      "    4  bar        2  bar        6\n",
      "    5  baz        3  baz        7\n",
      "    \n",
      "    Merge DataFrames df1 and df2 with specified left and right suffixes\n",
      "    appended to any overlapping columns.\n",
      "    \n",
      "    >>> df1.merge(df2, left_on='lkey', right_on='rkey',\n",
      "    ...           suffixes=('_left', '_right'))\n",
      "      lkey  value_left rkey  value_right\n",
      "    0  foo           1  foo            5\n",
      "    1  foo           1  foo            8\n",
      "    2  foo           5  foo            5\n",
      "    3  foo           5  foo            8\n",
      "    4  bar           2  bar            6\n",
      "    5  baz           3  baz            7\n",
      "    \n",
      "    Merge DataFrames df1 and df2, but raise an exception if the DataFrames have\n",
      "    any overlapping columns.\n",
      "    \n",
      "    >>> df1.merge(df2, left_on='lkey', right_on='rkey', suffixes=(False, False))\n",
      "    Traceback (most recent call last):\n",
      "    ...\n",
      "    ValueError: columns overlap but no suffix specified:\n",
      "        Index(['value'], dtype='object')\n",
      "    \n",
      "    >>> df1 = pd.DataFrame({'a': ['foo', 'bar'], 'b': [1, 2]})\n",
      "    >>> df2 = pd.DataFrame({'a': ['foo', 'baz'], 'c': [3, 4]})\n",
      "    >>> df1\n",
      "          a  b\n",
      "    0   foo  1\n",
      "    1   bar  2\n",
      "    >>> df2\n",
      "          a  c\n",
      "    0   foo  3\n",
      "    1   baz  4\n",
      "    \n",
      "    >>> df1.merge(df2, how='inner', on='a')\n",
      "          a  b  c\n",
      "    0   foo  1  3\n",
      "    \n",
      "    >>> df1.merge(df2, how='left', on='a')\n",
      "          a  b  c\n",
      "    0   foo  1  3.0\n",
      "    1   bar  2  NaN\n",
      "    \n",
      "    >>> df1 = pd.DataFrame({'left': ['foo', 'bar']})\n",
      "    >>> df2 = pd.DataFrame({'right': [7, 8]})\n",
      "    >>> df1\n",
      "        left\n",
      "    0   foo\n",
      "    1   bar\n",
      "    >>> df2\n",
      "        right\n",
      "    0   7\n",
      "    1   8\n",
      "    \n",
      "    >>> df1.merge(df2, how='cross')\n",
      "       left  right\n",
      "    0   foo      7\n",
      "    1   foo      8\n",
      "    2   bar      7\n",
      "    3   bar      8\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(pd.merge)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
